import datetime
import json

import cx_Oracle
import pandas as pd

"""递归查询oracle存储关联的存储和表"""

user = "pigpos"
# user = "bachfarm"
passwd = "P1g#2023pos"
# passwd = "fA2#3!KXt8L"
listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
# listener = '10.240.24.135/NMDCFARM'
# listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
# listener = 'CTCNYTS.cpchina.cn/CTCNYTS'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
table_set = {'1'}
path = "C:/Users/asyf/Desktop/"


def export_excel():
    export = []
    tabel_list = list(table_set)
    list.sort(tabel_list)
    for table_name in tabel_list:
        t = {'table_name': table_name}
        export.append(t)
        # print(table_name)
    print(export)
    pf = pd.DataFrame(list(export))
    order = ['table_name']
    pf = pf[order]
    writer = pd.ExcelWriter(path + 'table_name.xlsx')
    pf.to_excel(writer, index=False)
    writer.close()
    # pf.to_excel(path + 'table_name.xlsx', index=False)


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


def add2set(field_list):
    for f in field_list:
        referenced_type = f['REFERENCED_TYPE']
        type = 'TABLE'  # 表 -> 导出表使用
        # type = 'PROCEDURE'  # 存储过程 -> 导出存储过程
        if referenced_type == type or referenced_type == 'SYNONYM':
            table_set.add(f['REFERENCED_NAME'])


def filter_procedure(field_list: list):
    # 过滤存储过程->查询存储的调用深度
    field_list_new = []
    for i in field_list:
        # print(type(i))  # dict
        if i['REFERENCED_TYPE'] == 'PROCEDURE':
            field_list_new.append(i)
    return field_list_new


def get_line_aray(name, procedure_name):
    sql = "SELECT rownum,t.* FROM user_source t WHERE NAME = '{}' ORDER BY line".format(procedure_name)
    field_list = select(sql)
    field_list = json.loads(field_list)
    line_list = []
    note_flag = 0
    for f in field_list:
        text = f['TEXT'].upper().strip()
        text = text.replace('\n', '')
        text = text.replace('\t', '')
        text = text.replace(' ', '')

        rownum = f['ROWNUM']
        if text.startswith('--'):
            if '*/' in text:
                note_flag = 0  # 解决问题2，如果--里面有*/，前面所有的/*注释就失效了
            continue  # 解决问题1
        # 判断是否注释
        # 可能失效的场景
        # 1^--/*（已解决，--开头就终止）
        # 2sql片段--sql片段/*（部分解决，应该判断--后面有/*或者*/）
        if '/*' in text:
            note_flag += 1
        if '*/' in text:
            note_flag -= 1

        # 判断是否包含
        if name.upper() in text and note_flag == 0:
            line_list.append(rownum)
    return line_list


def find_dep(table_name):
    sql = "SELECT * FROM user_dependencies t WHERE t.name = '" + table_name + "'"
    # 只查询存储
    # sql += " and t.REFERENCED_TYPE = 'PROCEDURE'"

    sql += " order by t.REFERENCED_TYPE desc"
    field_list = select(sql)
    field_list = json.loads(field_list)
    add2set(field_list)
    for f in field_list:
        referenced_type = f['REFERENCED_TYPE']
        # 查询使用的行数
        line_list = get_line_aray(f['REFERENCED_NAME'], f['NAME'])
        f['line_list'] = line_list
        f['first_line'] = 0
        if len(line_list) > 0:
            f['first_line'] = line_list[0]

        if referenced_type == 'PROCEDURE' or referenced_type == 'FUNCTION':
            # 递归查询
            rs = find_dep(f['REFERENCED_NAME'])
            f['dependencies'] = json.loads(rs)
    # 过滤存储过程->查询存储的调用深度
    # field_list = filter_procedure(field_list)

    # 按照行号排序
    field_list = sorted(field_list, key=lambda x: x['first_line'], reverse=False)
    return json.dumps(field_list)


if __name__ == '__main__':
    print('开始查询')
    # t = 'FR_SW_JOB_CHECK_LIST'
    # t = 'FR_SP_SW_GEN_RPT_EFF_PORCITEC'
    # t = 'FR_SW_JOB_COSTING_FEED'
    # t = 'FR_SP_SW_REPORT_127'
    # t = 'V1_SWC420_CAL_FINISH_BREED'
    t = 'FR_SW_JOB_COST'
    # t = 'FR_SP_SW_GEN_REPORT_CORP_ALL'
    # t = 'FC_FR_BA03_CAL_VAT'
    # t = 'FR_SW_JOB_COSTING_GEN_GL_SAP'
    out_file = open(path + t + ".json", "w")
    try:
        r = find_dep(t)
        print(r)
        # 将json数据写到文件
        json.dump(json.loads(r), out_file, indent=6)
        # 将数据表导出到excel
        export_excel()

    finally:
        out_file.close()
        disconnect()
